﻿using System;
using System.Collections.Generic;

using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Windows.Forms;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
using System.Drawing;

namespace INSAForManagers
{
    class Util
    {
        //엑셀 객체
        private static Excel.Application oExcel = null;

        public static Excel.Application OExcel
        {
            get { return Util.oExcel; }
            set { Util.oExcel = value; }
        }

        private static Excel.Workbook oWorkBook;

        public static Excel.Workbook OWorkBook
        {
            get { return Util.oWorkBook; }
            set { Util.oWorkBook = value; }
        }


        //워크 시트 객체
        private static Excel.Worksheet oSheet;

        public static Excel.Worksheet OSheet
        {
            get { return Util.oSheet; }
            set { Util.oSheet = value; }
        }

        //레인지 객체
        private static Excel.Range oRange;

        public static Excel.Range ORange
        {
            get { return Util.oRange; }
            set { Util.oRange = value; }
        }

        //엑셀파일 이름
        private static string xlsFileName;

        public static string XlsFileName
        {
          get { return Util.xlsFileName; }
          set { Util.xlsFileName = value; }
        }


        
        //엑셀 열기
        public static Excel.Worksheet openXls(string xls_name)
        {
            xlsFileName = xls_name;
            bool b = File.Exists(xls_name);
            if (b)
            {
                try
                {
                    OExcel = new Excel.ApplicationClass();                    
                    OExcel.DisplayAlerts = false;
                    
                    OWorkBook = OExcel.Workbooks.Open(xlsFileName, 0, false, 5, Config.xls_password, Config.xls_password, false, Excel.XlPlatform.xlWindows, "", true, true, 0, true, false, false);

                    OSheet = (Excel.Worksheet)OWorkBook.Sheets[1];

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "오류", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    OSheet = null;
                }
            }
            else {
                MessageBox.Show(xls_name + "프로그램의 구성 요소 중 일부가 삭제되거나 이동되었습니다.   ", "오류", MessageBoxButtons.OK, MessageBoxIcon.Error);               
            }

            return OSheet;

        }

        //엑셀 닫기
        public static void closeXls()
        {
            if (OExcel != null)
            {                
                OWorkBook.Close(false, Missing.Value, false);                
                OExcel.Quit();                
                OExcel = null;                
            }
        }

        public static void changeSheet(int i)
        {
            oSheet = (Excel.Worksheet)OWorkBook.Sheets[i];
        }

        //다면평가대상자 추가
        public static void addMTargetAdd(string pcode, string name, string sex, string subject)
        {
            if (DBUtil.isExistedMTarget(pcode) == false)
            {

                //DB에 입력한 후 엑셀에 값 세팅하기
                DBUtil.insertMTargetInfo(pcode, name, sex, subject);

            }
            else 
            {
                //DB에 입력한 후 엑셀에 값 세팅하기
                DBUtil.updateMTargetInfo(pcode, name, sex, subject);
            }
        }

        //다면평가대상자 전체 엑셀에 표시
        public static void addMTargetXls()
        { 
            //M_TARGET테이블 레코드 개수 만큼 반복해서 값을 입력함
            ArrayList list = DBUtil.getMTargetList();

            try
            {
                int i = 4;
                //루프
                foreach(ArrayList tmp in list)
                {
                    
                    Util.OSheet.Cells[i, 1] = i - 3;
                    Util.OSheet.Cells[i, 2] = (string)tmp[0];
                    Util.OSheet.Cells[i, 3] = (string)tmp[1];
                    Util.OSheet.Cells[i, 4] = (string)tmp[2];
                    Util.OSheet.Cells[i, 5] = (string)tmp[3];
                    i++;
                    
                }

            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message + "\n" + ex.ToString());

            }

        }


        public static double getMaxElement(double[] d) {
            double max = 0.0;

            int cnt = d.Length;

            for (int i = 0; i < cnt; i++)
            {
                if (d[i] >= max )
                {
                    max = d[i];
                }
            }
            return max;
        }

        public static double getMinElement(double[] d)
        {
            double min = 100.0;

            int cnt = d.Length;            
   
            for (int i = 0; i < cnt; i++)
            {
                if (d[i] == 0.0) continue;

                if (d[i] <= min)
                {
                    min = d[i];
                }
            }
            return min;
        }

        public static double getSumElements(double[] d) 
        {

            double sum = 0;
            int cnt = d.Length;
            for (int i = 0; i < cnt; i++)
            {
                if (d[i] != 0.0)
                {
                    //MessageBox.Show(d.GetValue(i).ToString());
                    sum += d[i];
                }
            }

            return sum;
        }

        public static int getSortNum(double[] total, double d)
        {
            int num = 0;
            for (int i = 0; i < total.Length; i++)
            {
                if (total[i] > d)
                {
                    num++;
                }
            }

            return num + 1;
        }

        //동점 존재 여부
        public static int[] existedSamePoint(double[] total)
        {            
            int[] index = new int[2];
            for (int i = 0; i < total.Length; i++)
            {                
                for (int j = i + 1; j < total.Length; j++)
                {
                    if (total[i] == total[j])
                    {
                        index[0] = i;
                        index[1] = j;
                        break;
                    }
                }
            }
            return index;
        }

        //랜덤 범위
        public static double getRandomRange(Int16 i)
        {
            if (i <= 10)
            {
                return 0.3;
            }
            else if (i > 10 && i <= 20)
            {
                return 0.2;
            }
            else
            {
                return 0.1;
            }
        }

        //기본경력 만점 연수
        public static int getBasicCareerMaxYear()
        {
            return 16;
        }

        //초과경력 만점 연수
        public static int getAdditiveCareerMaxYear()
        {
            return 5;
        }


        /// <summary>
        /// 두 날짜 사이의 {월, 일}을 계산한다
        /// </summary>
        /// <param name="dt1">시작일자</param>
        /// <param name="dt2">종료일자</param>
        /// <returns>{월, 일}</returns>
        public static int[] getMonthCount(DateTime dt1, DateTime dt2)
        {
            if (dt1 == null || dt2 == null) return new int[] { 0, 0 };
            if (dt1.CompareTo(dt2) >= 0) return new int[] { 0, 0 };

            int[] diff = new int[2];

            int NumOfMonth = 0;
            int NumOfDay = 0;

            int sYear = dt1.Year;           //시작년도
            int eYear = dt2.Year;           //종료년도

            int sMonth = dt1.Month;         //시작월
            int eMonth = dt2.Month;         //종료월

            int sDay = dt1.Day;             //시작일
            int eDay = dt2.Day;             //종료일

            int difYear = eYear - sYear;    //연도차
            NumOfMonth = difYear * 12;      //연도차 -> 월수계산

            int difMonth = eMonth - sMonth; //월차(시작월, 끝월 제외)
            NumOfMonth += difMonth;             //월수계산에 합산


            //시작일이 1 이면 종료일이 해당 월의 마지막 날이면 월 1증가
            if (sDay == 1 && eDay == DateTime.DaysInMonth(eYear, eMonth))
            {
                NumOfMonth++;
            }

            //1일 시작 - 월중 종료 
            if (sDay == 1 && eDay != DateTime.DaysInMonth(eYear, eMonth))
            {
                NumOfDay = eDay;
            }

            //임의 시작 - 월말 종료 
            if (sDay != 1 && eDay == DateTime.DaysInMonth(eYear, eMonth))
            {
                NumOfDay = DateTime.DaysInMonth(sYear, sMonth) - sDay + 1;
            }

            //월중 시작 - 월중 종료
            if (sDay != 1 && eDay != DateTime.DaysInMonth(eYear, eMonth))
            {
                NumOfDay += (DateTime.DaysInMonth(sYear, sMonth) - sDay + 1 + eDay);
                if (NumOfDay >= 30)
                {
                    NumOfDay = NumOfDay - 30;
                }
                else
                {
                    NumOfMonth--;
                }
            }

            diff[0] = NumOfMonth;
            diff[1] = NumOfDay;

            return diff;
        }

        /// <summary>
        /// 두 일자 사이의 {월수,일수} 계산하되 제외기간을 고려한다.
        /// </summary>
        /// <param name="dt1">근무시작일자</param>
        /// <param name="dt11">대상시작일자</param>
        /// <param name="dt2">근무종료일자</param>
        /// <param name="dt21">대상종료일자</param>
        /// <returns>{월수,일수}</returns>
        public static int[] getMonthCount(DateTime dt1, DateTime dt11, DateTime dt2, DateTime dt21)
        {
            if (dt1 == null || dt2 == null || dt11 == null || dt21 == null) return null;

            int numOfMonth = 0;
            int numOfDay = 0;

            int[] diff = new int[2];
            int[] diff_ex = new int[2];

            //근무 월/일
            diff = getMonthCount(dt1, dt2);

            //제외 월/일
            diff_ex = getMonthCount(dt11, dt21);

            numOfMonth = diff[0] - diff_ex[0];

            numOfDay = diff[1] - diff_ex[1];

            if (numOfDay < 0)
            {
                numOfMonth--;
                numOfDay += 30;
            }

            return new int[2] { numOfMonth, numOfDay };
        }

        /// <summary>
        /// 구분/등급별의 주어진 월수에 대한 평정점 계산하기
        /// </summary>
        /// <param name="careerType">구분</param>
        /// <param name="careerLevel">등급</param>
        /// <param name="NumOfMonth">월수</param>
        /// <returns>평정점</returns>
        public static double getCareerPointByMonth(string careerType, string careerLevel, int NumOfMonth)
        {
            double d = getCareerMonthPoint(careerType, careerLevel); ;
            double res = d * NumOfMonth;
            return res;
        }

        /// <summary>
        /// 구분/등급별의 주어진 일수에 대한 평정점 계산하기
        /// </summary>
        /// <param name="careerType">구분</param>
        /// <param name="careerLevel">등급</param>
        /// <param name="days">일수</param>
        /// <returns>평정점</returns>
        public static double getCareerPointByDay(string careerType, string careerLevel, int days)
        {
            double d = getCareerDayPoint(careerType, careerLevel);
            double res = d * days;
            return res;
        }

        /// <summary>
        /// 구분/등급별 월 평정점 구하기
        /// </summary>
        /// <param name="careerType">구분</param>
        /// <param name="careerLevel">등급</param>
        /// <returns>월 평정점</returns>
        public static double getCareerMonthPoint(string careerType, string careerLevel)
        {
            double d = 0.0;
            if (careerType.Equals("기본경력"))
            {
                switch (careerLevel)
                {
                    case "가경력":
                        d = 0.3333;
                        break;
                    case "나경력":
                        d = 0.3125;
                        break;
                    case "다경력":
                        d = 0.2916;
                        break;
                }
            }
            else if (careerType.Equals("초과경력"))
            {
                switch (careerLevel)
                {
                    case "가경력":
                        d = 0.1000;
                        break;
                    case "나경력":
                        d = 0.0833;
                        break;
                    case "다경력":
                        d = 0.0666;
                        break;
                }
            }

            return d;
        }

        /// <summary>
        /// 경력상한연수(매년 수정 해야 함)
        /// </summary>
        /// <param name="careerType">구분</param>
        /// <returns>상한연수</returns>
        public static int getCareerUpperLimitYear(string careerType)
        {
            int limitYear = 0;
            if (careerType.Equals("기본경력"))
            {
                limitYear = 16;
            }
            else if (careerType.Equals("초과경력"))
            {
                limitYear = 5;
            }
            return limitYear;
        }

        /// <summary>
        /// 구분/등급별 경력 상한점(매년 수정해야 함)
        /// </summary>
        /// <param name="careerType">구분</param>
        /// <param name="careerLevel">등급</param>
        /// <returns>상한점</returns>
        public static double getCareerMaxPoint(string careerType, string careerLevel)
        {
            double maxPoint = 0.0;
            if (careerType.Equals("기본경력"))
            {
                switch (careerLevel)
                {
                    case "가경력":
                        maxPoint = 64.0;
                        break;
                    case "나경력":
                        maxPoint = 60.0;
                        break;
                    case "다경력":
                        maxPoint = 56.0;
                        break;
                }
            }
            else if (careerType.Equals("초과경력"))
            {
                switch (careerLevel)
                {
                    case "가경력":
                        maxPoint = 6.0;
                        break;
                    case "나경력":
                        maxPoint = 5.0;
                        break;
                    case "다경력":
                        maxPoint = 4.0;
                        break;
                }
            }
            return maxPoint;
        }

        /// <summary>
        /// 구분/등급별 1일에 대한 평정점 구하기
        /// </summary>
        /// <param name="careerType">구분</param>
        /// <param name="careerLevel">등급</param>
        /// <returns>일 평정점</returns>
        public static double getCareerDayPoint(string careerType, string careerLevel)
        {
            double d = 0.0;
            if (careerType.Equals("기본경력"))
            {
                switch (careerLevel)
                {
                    case "가경력":
                        d = 0.0111;
                        break;
                    case "나경력":
                        d = 0.0104;
                        break;
                    case "다경력":
                        d = 0.0097;
                        break;
                }
            }
            else if (careerType.Equals("초과경력"))
            {
                switch (careerLevel)
                {
                    case "가경력":
                        d = 0.0033;
                        break;
                    case "나경력":
                        d = 0.0027;
                        break;
                    case "다경력":
                        d = 0.0022;
                        break;
                }
            }

            return d;
        }

        //public static double getEduPointByTime(string type, string jobLevel, double point, int time)
        public static double getEduPointByTime(string type,  double point, int time)
        {
            double d = 0.0;

            if (type.Equals("자격연수"))
            {
                d = 9 - (100 - point) * 0.05;
            }
            else
            {
                if (time > 0)
                {
                    d = 0.0;
                }
                else
                {
                    d = 6.0 * (point / 100);
                }
            }
            //if(jobLevel.Equals("교사"))
            //{
            //    if (type.Equals("자격연수"))
            //    {
            //        d = 9 - (100 - point) * 0.05;
            //    }
            //    else
            //    {
            //        if (time == 0)
            //        {
            //            d = 6.0 * (point / 100);
            //        }
            //        else if (time == 1 || time == 2)
            //        {
            //            d = 6.0;
            //        }
            //        else
            //        {
            //            d = 0.0;
            //        }
            //    }
            //}
            //else
            //{
            //    if (type.Equals("자격연수"))
            //    {
            //        d = 9 - (100 - point) * 0.05;
            //    }
            //    else
            //    {
            //        if (time > 0)
            //        {
            //            d = 0.0;
            //        }
            //        else
            //        {
            //            d = 6.0 * (point / 100);
            //        }
            //    }
            //}

            return d;
        }

        /// <summary>
        /// 규모, 등급, 참여인원을 조건으로 연구점수를 구한다.
        /// </summary>
        /// <param name="scale">규모</param>
        /// <param name="rating">등급</param>
        /// <param name="num">참여인원</param>
        /// <returns>연구점수</returns>
        public static double getResearchPoint(string scale, string rating, string num)
        {
            double d = 0.0;

            if (scale.Equals("전국"))
            {
                switch (rating)
                {
                    case "1등급":
                        d = 1.50;
                        break;
                    case "2등급":
                        d = 1.25;
                        break;
                    case "3등급":
                        d = 1.00;
                        break;
                }
            }
            else
            {
                switch (rating)
                {
                    case "1등급":
                        d = 1.00;
                        break;
                    case "2등급":
                        d = 0.75;
                        break;
                    case "3등급":
                        d = 0.50;
                        break;
                }
            }


            switch (num)
            {
                case "2인":
                    d *= 0.7;
                    break;
                case "3인":
                    d *= 0.5;
                    break;
                case "4인이상":
                    d *= 0.3;
                    break;
            }

            return d;
        }

        public static double getScholarPoint(string type, string relation)
        {
            double d = 0.0;
            if (type.Equals("석사"))
            {
                if(relation.Equals("직무관련"))
                {
                    d = 1.5;
                }
                else
                {
                    d = 1.0;
                }
            }
            else
            {
                if (relation.Equals("직무관련"))
                {
                    d = 3.0;
                }
                else
                {
                    d = 1.5;
                }
            }
            return d;
        }

        public static string getShortSchoolName(string schoolName)
        {
            string _schoolName = "";

            if (schoolName.Length == 0) return "";

            schoolName = schoolName.Replace("경북대학교사범대학", "경대사대");

            if (schoolName.IndexOf("중학교") > 0)
            {
                _schoolName = schoolName.Replace("학교", "");
            }
            else if (schoolName.IndexOf("정보고등학교") > 0)
            {
                _schoolName = schoolName.Replace("정보고등학교", "정보고");
            }
            else if (schoolName.IndexOf("공업고등학교") > 0)
            {
                _schoolName = schoolName.Replace("공업고등학교", "공고");
            }
            else if (schoolName.IndexOf("고등학교") > 0)
            {
                _schoolName = schoolName.Replace("등학교", "");
            }
            else
            {
                _schoolName = schoolName;
            }

            return _schoolName;
        }

        /// <summary>
        /// 특수문자를 escape 시킨다.
        /// </summary>
        /// <param name="str">대상문자열</param>
        /// <returns>특수문자를 제거한 문자열</returns>
        public static string removeSpecialChars(string str)
        {
            string[] chars = new string[] { ",", ".", "/", "!", "@", "#", "$", "%", "^", "&", "*", "'", "\"", ";", "_", "(", ")", ":", "|", "[", "]" };
            for (int i = 0; i < chars.Length; i++)
            {
                if (str.Contains(chars[i]))
                {
                    str = str.Replace(chars[i], "");
                }
            }
            return str;
        }

        /// <summary>
        /// 구분, 직위, 점수, 회차 정보를 받아서 연수성적평정점을 계산한다.
        /// </summary>
        /// <param name="type">구분(자격, 직무연수)</param>
        /// <param name="jobLevel">직위</param>
        /// <param name="point">점수</param>
        /// <param name="time">회차</param>
        /// <returns>평정점</returns>
        public static double getEduPointByTime(string type, string jobLevel, double point, int time)
        {
            double d = 0.0;

            if (jobLevel.Equals("교사"))
            {
                if (type.Equals("자격연수"))
                {
                    d = 9 - (100 - point) * 0.05;
                }
                else
                {
                    if (time == 0)
                    {
                        d = 6.0 * (getConvertedScore(point) / 100.0);
                    }
                    else if (time == 1 || time == 2)
                    {
                        d = 6.0;
                    }
                    else
                    {
                        d = 0.0;
                    }
                }
            }
            else
            {
                if (type.Equals("자격연수"))
                {
                    d = 9 - (100 - point) * 0.05;
                }
                else
                {
                    if (time > 0)
                    {
                        d = 0.0;
                    }
                    else
                    {
                        d = 6.0 * (getConvertedScore(point) / 100.0);
                    }
                }
            }

            return d;
        }

        /// <summary>
        /// 연수성적평정 시 환산점수를 반환한다.
        /// </summary>
        /// <param name="originScore">원점수</param>
        /// <returns>환산점수</returns>
        public static double getConvertedScore(double originScore)
        {
            if (originScore > 95.0)
            {
                return 100.0;

            }
            else if (originScore > 90.0)
            {
                return 95.0;
            }
            else if (originScore > 85.0)
            {
                return 90.0;
            }
            else
            {
                return 85.0;
            }
        }

        /// <summary>
        /// DataGridView에서 주어진 행수만큼 주어진 색깔로 설정한다.
        /// </summary>
        /// <param name="dgv">DataGridView</param>
        /// <param name="rowCount">행수</param>
        /// <param name="color">색깔</param>
        public static void setBackColor(DataGridView dgv, int rowCount, Color color)
        {
            for (int i = 0; i < rowCount; i++)
            {
                dgv.Rows[i].DefaultCellStyle.BackColor = color;
            }
        }
    }
}
